<?php

version_compare(PHP_VERSION, '7.0.0', '>=') or die('required PHP version greater than 7.0.0');


class QueryMarketingRegisterUsers
{

    const DB_CONFIG = [
        'dts' => [
            'host' => '10.168.177.85',
            'username' => 'reader',
            'passwd' => 'Ut90CVgeOwwwfL%d',
            'dbname' => 'dts',
            'port' => '3311',
        ],
        'users' => [
            'host' => '10.168.177.85',
            'username' => 'reader',
            'passwd' => 'Ut90CVgeOwwwfL%d',
            'dbname' => 'users',
            'port' => '3306',
        ],
    ];

    const DB_DTS = 'dts';
    const DB_USERS = 'users';

    /**
     * time configurations
     */
    const DEFAULT_DATE_STR = '-30 days';
    const DEFAULT_DATE_FORMAT = 'Y-m-d H:i:s';
    const DEFAULT_TIME_ZONE = 'PRC';

    private $showColumns = [
        'id' => '编号',
        'username' => '用户名',
        'realname' => '名字',
        'mobile' => '电话',
        'create_at' => '注册时间',
        'update_at' => '信息更新时间',
        'register_sitesource' => '注册来源',
        'last_login_time' => '最后登录',
        'last_login_sitesource' => '最后登录来源',
        'msg_sent_time' => '短信发送时间',
    ];

    private $dbInstances = [];
    private $marketingPhones = [];
    private $marketingSmsLog = [];
    private $lastQueryMembers = [];
    private $queryTime;

    /**
     * QueryMarketingRegisterUsers constructor.
     * @param string|null $queryTime
     */
    public function __construct(string $queryTime = null)
    {
        if(preg_match('/^(?:\d{4}-\d{2}-\d{2}\s(?:\d{2}:){2}\d{2})$/', $queryTime)){
            $this->queryTime = $queryTime;
        }else{
            $this->queryTime = $this->getDefaultDate();
        }
    }

    /**
     * @return string
     */
    private function getDefaultDate()
    {
        return (new DateTime(static::DEFAULT_DATE_STR, new DateTimeZone(static::DEFAULT_TIME_ZONE)))->format(static::DEFAULT_DATE_FORMAT);
    }

    /**
     * @param string|null $timeStr
     */
    public function queryPossibleUsersByTime(string $timeStr = null)
    {
        $timeStr === null and $timeStr = $this->queryTime;
        if(($this->marketingPhones = $this->getSentSmsPhoneByTime($timeStr)) && ($membersInfo = $this->getRegisterUserByPhone($this->marketingPhones, $timeStr))){
            $membersInfo = $this->getSmsSentLaterThanRegister($this->marketingSmsLog, $membersInfo);
            array_unshift($membersInfo, $this->showColumns);
            foreach ((array)$membersInfo as $member) {
                !isset($member['msg_sent_time']) and $member['msg_sent_time'] = $this->marketingSmsLog[$member['mobile']]['create_at'] ?? '';
                echo $this->formatOutput($member);
            }
            $lastMemberInfo = array_pop($membersInfo);
            //var_dump(array_slice($this->marketingSmsLog, -10, null, true));
            printf('total:%d, time:start@%s, end@%s', count($membersInfo), $this->queryTime, $lastMemberInfo['create_at']);
            exit;
        }
        var_dump(['receiverPhones' => $this->marketingPhones, 'registed' => $this->lastQueryMembers]);
    }

    /**
     * formatOutPut
     *
     * @param  array  $data
     * @return string
     */
    private function formatOutput(array $data)
    {
        return count($data) ? implode(', ', $this->filterOutputData($data)).PHP_EOL : '';
    }

    /**
     * filterOutputDatap
     *
     * @param  array      $data
     * @param  array|null $allowedKeys
     * @return array
     */
    private function filterOutputData(array $data, array $allowedKeys = null)
    {
        $allowedKeys === null and $allowedKeys = $this->showColumns;
        return count($data) && count($allowedKeys) ? array_intersect_key($data, $allowedKeys) : $data;
    }

    /**
     * updateShowColumns
     *
     * @param  array  $columns
     * @return $this
     */
    public function updateShowColumns(array $columns)
    {
        if(count($columns)){
            $this->showColumns = array_merge($this->showColumns, $columns);
        }
        return $this;
    }

    /**
     * @param string $phone
     * @return array|bool|mixed|mysqli_result
     */
    public function queryPossibleUsersByPhone(string $phone)
    {
        return $this->getRegisterUserByPhone([$phone]);
    }

    /**
     * @param string $dbName
     * @param bool $new
     * @return mixed
     * @throws Exception
     */
    public function getDbInstance(string $dbName, bool $new = false)
    {
        if(empty($this->dbInstances[$dbName])){
            if(!array_key_exists($dbName, static::DB_CONFIG)){
                throw new Exception('this db is not set yet');
            }
            $this->dbInstances[$dbName] = new MySQLi(...array_values(static::DB_CONFIG[$dbName]));
            if($this->dbInstances[$dbName]->connect_error){
                throw new Exception('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
            }
            $this->dbInstances[$dbName]->set_charset('utf8');
        }
        return $this->dbInstances[$dbName];
    }

    /**
     * @param string|null $timeStr
     * @return array
     */
    public function getSentSmsPhoneByTime(string $timeStr = null)
    {
        empty($timeStr) and $timeStr = $this->queryTime;
        //$sql = "SELECT * FROM wkd_marketing_sms_sent_log WHERE create_at > '{$timeStr}' ORDER BY id ASC";
        $sql = "SELECT * FROM wkd_marketing_sms_sent_log WHERE create_at > '{$timeStr}' GROUP BY phone ORDER BY id ASC";
        if($data = $this->query($sql, $this->getDbInstance(static::DB_DTS))){
            //group by version
            $phones = array_column($data, 'phone');
            $this->marketingSmsLog = array_combine($phones, $data);
            $this->marketingPhones = array_unique($phones);

            // $tmpArr = [];
            // foreach ($data as $val) {
            //     $tmpArr[$val['mobile']] = $val;
            // }
            // $this->marketingSmsLog = $tmpArr;
            // $this->marketingPhones = array_keys($tmpArr);
            unset($data, $phones, $tmpArr);
        }
        return $this->marketingPhones;
    }

    /**
     * getSmsSentLaterThanRegister
     *
     * @param  array  $smsSentInfo
     * @param  array  $membersInfo
     * @return array
     */
    private function getSmsSentLaterThanRegister(array $smsSentInfo, array $membersInfo)
    {
        return array_filter($membersInfo, function($member, $phone) use($smsSentInfo){
            return isset($smsSentInfo[$phone]) && $smsSentInfo[$phone]['create_at'] < $member['create_at'];
        }, ARRAY_FILTER_USE_BOTH);
    }

    /**
     * @param array $phones
     * @param string $timeStr
     * @return array|bool|mixed|mysqli_result
     */
    public function getRegisterUserByPhone(array $phones = [], string $timeStr = '')
    {
        $members = [];
        if(count($phones) > 0){
            empty($timeStr) and $timeStr = $this->queryTime;
            $sql = 'SELECT * FROM '.static::DB_USERS.' WHERE mobile IN ("'.implode('","', $phones).'") AND create_at > "'.$timeStr.'" ORDER BY id ASC';
            if($members = $this->query($sql, $this->getDbInstance(static::DB_USERS))){
                $this->lastQueryMembers = array_combine(array_column($members, 'mobile'), $members);
                unset($members);
                //$this->members = array_replace((array)$this->members, $members);
            }
        }
        return $this->lastQueryMembers;
    }

    /**
     * @param string $sql
     * @param MySQLi $dbInstance
     * @return bool|mixed|mysqli_result
     */
    private function query(string $sql, MySQLi $dbInstance)
    {
        if($result = $dbInstance->query($sql)){
            return $data = $result->fetch_all(MYSQLI_ASSOC);
        }
        return $result;
    }
}
// $timeStr = '2016-11-25 12:00:00';
$timeStr = null;
(new QueryMarketingRegisterUsers($timeStr))->queryPossibleUsersByTime();
//(new QueryMarketingRegisterUsers())->queryPossibleUsersByTime();
